package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.HospInfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.BaseGoodsByHospDao;
import com.hsnn.medstgmini.pro.base.model.BaseGoods;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class BaseGoodsByHospDaoImpl extends BaseHibernateDAO implements
		BaseGoodsByHospDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 生产企业查询商品目录
	 * @author 单大伟
	 * @date 2015年6月15日
	 * @param @param baseDelrelation
	 * @param @return
	 */
	@Override
	public Pagination getBaseGoodsListJsonByComp(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.`status`, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType, a.is_healthcare isHealthcare, ");
		sql.append(" a.is_agriculture isAgriculture, a.is_lowprice isLowprice, a.is_smallprice isSmallprice, ");
		sql.append(" a.is_smallsource isSmallsource, a.is_longtime_plan isLongtimePlan, a.drug_cat drugCat, ");
		sql.append(" a.pro_price_limit proPriceLimit, a.retail_price_limit retailPriceLimit, ");
		sql.append(" a.hang_price hangPrice, a.ref_price refPrice, a.healthcare_price healthcarePrice, ");
		sql.append(" a.qualityspec_name qualityspecName,a.drug_medicine drugMedicine, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName FROM base_goods_item a ");
		sql.append(" WHERE a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		sql.append(" AND a.prod_compcode = :compCode ");

		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != form) {
			params.put("compCode", form.getCompCode());
			
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + form.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(form.getProjName())) {
				sql.append(" and a.proj_name LIKE :projName ");
				params.put("projName", "%" + form.getProjName() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compName) ");
				params.put("compName", "%" + form.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(form.getGoodsCode())) {
				sql.append(" and a.goods_code = :goodsCode ");
				params.put("goodsCode", form.getGoodsCode());
			}
			if (StringUtils.isNotBlank(form.getDrugCode())) {
				sql.append(" and a.drug_code = :drugCode ");
				params.put("drugCode", form.getDrugCode());
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompName) ");
				params.put("catCompName", "%" + form.getCatCompName() + "%");
			}
			if (form.getIsLowprice() != null) {
				sql.append(" and a.is_lowprice = :isLowprice ");
				params.put("isLowprice", form.getIsLowprice());
			}
			if (form.getIsSmallprice() != null) {
				sql.append(" and a.is_smallprice = :isSmallprice ");
				params.put("isSmallprice", form.getIsSmallprice());
			}
			if (form.getIsSmallsource() != null) {
				sql.append(" and a.is_smallsource = :isSmallsource ");
				params.put("isSmallsource", form.getIsSmallsource());
			}
			if (form.getGoodsSource() != null) {
				sql.append(" and a.goods_source = :goodsSource ");
				params.put("goodsSource", form.getGoodsSource());
			}
			if (form.getStatus() != null) {
				sql.append(" and a.status = :status ");
				params.put("status", form.getStatus());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}

	/**
	 * @category 配送企业查询商品目录
	 */
	public Pagination getBaseGoodsListJsonByDel(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.`status`, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType, a.is_healthcare isHealthcare, ");
		sql.append(" a.is_agriculture isAgriculture, a.is_lowprice isLowprice, a.is_smallprice isSmallprice, ");
		sql.append(" a.is_smallsource isSmallsource, a.is_longtime_plan isLongtimePlan, a.drug_cat drugCat, ");
		sql.append(" a.pro_price_limit proPriceLimit, a.retail_price_limit retailPriceLimit, ");
		sql.append(" a.hang_price hangPrice, a.ref_price refPrice, a.healthcare_price healthcarePrice, ");
		sql.append(" a.qualityspec_name qualityspecName, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor,a.drug_medicine drugMedicine, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName FROM base_goods_item a ");
		sql.append(" JOIN bid_projcompany b ON a.proj_id = b.proj_id ");
		sql.append(" WHERE a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		sql.append(" AND b.`status` = 1 AND b.proj_status = 1 AND b.comp_code = :compCode ");
		sql.append(" AND b.proj_starttime <= SYSDATE() AND b.proj_endtime >= SYSDATE() ");

		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != form) {
			params.put("compCode", form.getCompCode());
			
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + form.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(form.getProjName())) {
				sql.append(" and a.proj_name LIKE :projName ");
				params.put("projName", "%" + form.getProjName() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compName) ");
				params.put("compName", "%" + form.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(form.getGoodsCode())) {
				sql.append(" and a.goods_code = :goodsCode ");
				params.put("goodsCode", form.getGoodsCode());
			}
			if (StringUtils.isNotBlank(form.getDrugCode())) {
				sql.append(" and a.drug_code = :drugCode ");
				params.put("drugCode", form.getDrugCode());
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompName) ");
				params.put("catCompName", "%" + form.getCatCompName() + "%");
			}
			if (form.getIsLowprice() != null) {
				sql.append(" and a.is_lowprice = :isLowprice ");
				params.put("isLowprice", form.getIsLowprice());
			}
			if (form.getIsSmallprice() != null) {
				sql.append(" and a.is_smallprice = :isSmallprice ");
				params.put("isSmallprice", form.getIsSmallprice());
			}
			if (form.getIsSmallsource() != null) {
				sql.append(" and a.is_smallsource = :isSmallsource ");
				params.put("isSmallsource", form.getIsSmallsource());
			}
			if (form.getGoodsSource() != null) {
				sql.append(" and a.goods_source = :goodsSource ");
				params.put("goodsSource", form.getGoodsSource());
			}
			if (form.getStatus() != null) {
				sql.append(" and a.status = :status ");
				params.put("status", form.getStatus());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}

	/**
	 * @category 医院根据条件查询商品目录
	 * @param @return
	 */
	@Override
	public Pagination getBaseGoodsListByHosp(Pagination page, HospInfo hospInfo) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.`status`, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType, a.is_healthcare isHealthcare, ");
		sql.append(" a.is_agriculture isAgriculture, a.is_lowprice isLowprice, a.is_smallprice isSmallprice, ");
		sql.append(" a.is_smallsource isSmallsource, a.is_longtime_plan isLongtimePlan, a.drug_cat drugCat, ");
		sql.append(" a.pro_price_limit proPriceLimit, a.retail_price_limit retailPriceLimit, ");
		sql.append(" a.hang_price hangPrice, a.ref_price refPrice, a.healthcare_price healthcarePrice, ");
		sql.append(" a.qualityspec_name qualityspecName, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName,a.drug_medicine drugMedicine,c.dic_name goodsSourceName FROM base_goods_item a ");
		sql.append(" JOIN bid_projhospital b ON a.proj_id = b.proj_id ");
		sql.append(" left join dic_sysdic c on a.goods_source = c.dic_code and c.classify_code = 114 ");
		sql.append(" WHERE a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		sql.append(" AND b.`status` = 1 AND b.proj_status = 1 AND b.hosp_code = :hospCode ");
		sql.append(" AND b.proj_starttime <= SYSDATE() AND b.proj_endtime >= SYSDATE() ");
		sql.append(" AND ").append(AreaUtil.getDrugByArea(hospInfo.getArea().getAreaid(), "a.area_id"));
		sql.append(" AND (a.use_range = :useRange or a.use_range=0)");

		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (null != form) {
			params.put("useRange", hospInfo.getHospClassify());
			params.put("hospCode", form.getHospCode());
			
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + form.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(form.getProjName())) {
				sql.append(" and a.proj_name LIKE :projName ");
				params.put("projName", "%" + form.getProjName() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compName) ");
				params.put("compName", "%" + form.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(form.getGoodsCode())) {
				sql.append(" and a.goods_code = :goodsCode ");
				params.put("goodsCode", form.getGoodsCode());
			}
			if (StringUtils.isNotBlank(form.getDrugCode())) {
				sql.append(" and a.drug_code = :drugCode ");
				params.put("drugCode", form.getDrugCode());
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompName) ");
				params.put("catCompName", "%" + form.getCatCompName() + "%");
			}
			if (form.getIsLowprice() != null) {
				sql.append(" and a.is_lowprice = :isLowprice ");
				params.put("isLowprice", form.getIsLowprice());
			}
			if (form.getIsSmallprice() != null) {
				sql.append(" and a.is_smallprice = :isSmallprice ");
				params.put("isSmallprice", form.getIsSmallprice());
			}
			if (form.getIsSmallsource() != null) {
				sql.append(" and a.is_smallsource = :isSmallsource ");
				params.put("isSmallsource", form.getIsSmallsource());
			}
			if (form.getGoodsSource() != null) {
				if(form.getGoodsSource() != Constants.GOODS_SOURCE_BJBX){
					sql.append(" and a.goods_source = :goodsSource ");
					params.put("goodsSource", form.getGoodsSource());
				}
			}
			if (form.getStatus() != null) {
					sql.append(" and a.status = :status ");
					params.put("status", form.getStatus());
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}
	
	/**
	 * 
	 * @category 获取未加入调价计划商品列表
	 * @auth wangbing
	 * @return Pagination
	 * @date 2015年6月23日
	 */
	@SuppressWarnings("unchecked")
	public Pagination getPriceChangeDurgList(Pagination page) {
		Session session = super.getSession();
		String drugComp = page.getConditions().get("prodComp");
		String projName = page.getConditions().get("projName");
		String goodsCode = page.getConditions().get("goodsCode");
		String drugName = page.getConditions().get("drugName");
		String drugCode = page.getConditions().get("drugCode");
		String priceid = page.getConditions().get("priceid");
		
		StringBuffer sql = new StringBuffer()
		.append("	SELECT ")
		.append("	b.proj_name projName,b.drug_code drugCode,b.qualityspec_name drugName,b.form_name formName,")
		.append("	b.factor factor,b.unit_name unit,b.prod_compname prodComp,b.material_name materialName,b.prod_compcode prodCompId,")
		.append("	b.pro_price_limit proPriceLimit,b.drug_code drugId,")
		.append("	b.retail_price_limit retailPriceLimit,")
		.append("	b.goods_code goodsCode,b.spec_name specName")
		.append("	FROM base_goods_item  b  ")
		.append("   where b.proj_starttime < :currentTime and b.proj_endtime > :currentTime and b.status = 1  ")
		.append("   and b.goods_code not in (select a.goods_code from base_priceadjplandetail a")
		.append("   where a.price_adj_plan_id = :priceid)  ");
		
		
		StringBuffer goodsCountSql =  new StringBuffer()//商品总数
		.append(" select count(*) from base_goods_item  b where b.proj_starttime < :currentTime  ")
		.append(" and b.proj_endtime > :currentTime and b.status = 1 ");
		
		StringBuffer detailCountSql = new StringBuffer()//调价计划明细数
		.append(" select count(*) from base_goods_item  b left join base_priceadjplandetail a on a.goods_code = b.goods_code")
		.append(" where a.price_adj_plan_id = :priceid ");
		
		if(StringUtils.isNotBlank(drugComp)){
			sql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
			goodsCountSql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
			detailCountSql.append(" and (b.prod_compname like :drugComp or b.prod_compname_spel like :drugComp)");
		}
		if(StringUtils.isNotBlank(projName)){
			sql.append(" and b.proj_name like :projName ");
			goodsCountSql.append(" and b.proj_name like :projName ");
			detailCountSql.append(" and b.proj_name like :projName ");
		}
		if(StringUtils.isNotBlank(goodsCode)){
			sql.append(" and b.goods_code = :goodsCode ");
			goodsCountSql.append(" and b.goods_code = :goodsCode ");
			detailCountSql.append(" and b.goods_code = :goodsCode ");
		}
		if(StringUtils.isNotBlank(drugName)){
			sql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			goodsCountSql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
			detailCountSql.append(" and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName) ");
		}
		if(StringUtils.isNotBlank(drugCode)){
			sql.append(" and b.drug_code = :drugCode ");
			goodsCountSql.append(" and b.drug_code = :drugCode ");
			detailCountSql.append(" and b.drug_code = :drugCode ");
		}
		
		Query query = session.createSQLQuery(sql.toString());
		query.setString("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		Query goodsQuery = session.createSQLQuery(goodsCountSql.toString());
		goodsQuery.setString("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		Query detailQuery = session.createSQLQuery(detailCountSql.toString());
		query.setString("priceid", priceid);
		detailQuery.setString("priceid", priceid);
		
		if(StringUtils.isNotBlank(drugComp)){
			query.setString("drugComp", "%"+drugComp.trim()+"%");
			goodsQuery.setString("drugComp", "%"+drugComp.trim()+"%");
			detailQuery.setString("drugComp", "%"+drugComp.trim()+"%");
		}
		if(StringUtils.isNotBlank(projName)){
			query.setString("projName", "%"+projName.trim()+"%");
			goodsQuery.setString("projName", "%"+projName.trim()+"%");
			detailQuery.setString("projName", "%"+projName.trim()+"%");
		}
		if(StringUtils.isNotBlank(goodsCode)){
			query.setString("goodsCode", goodsCode.trim());
			goodsQuery.setString("goodsCode", goodsCode.trim());
			detailQuery.setString("goodsCode", goodsCode.trim());
		}
		if(StringUtils.isNotBlank(drugName)){
			query.setString("drugName", "%"+drugName.trim()+"%");
			goodsQuery.setString("drugName", "%"+drugName.trim()+"%");
			detailQuery.setString("drugName", "%"+drugName.trim()+"%");
		}
		if(StringUtils.isNotBlank(drugCode)){
			query.setString("drugCode", drugCode.trim());
			goodsQuery.setString("drugCode", drugCode.trim());
			detailQuery.setString("drugCode", drugCode.trim());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		page.setRows(list);
		
		int goodsCount = Integer.parseInt(goodsQuery.uniqueResult().toString());
		int detailCount = Integer.parseInt(detailQuery.uniqueResult().toString());
		int records = goodsCount - detailCount;
		page.setRecords(records);
		return page;
	}
}